/**
 * 
 */
package com.hexacta.hrs.repositories.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.vectrics.employee.Employee;
import org.vectrics.employee.MedicalInsurance;
import org.vectrics.user.User;

import com.hexacta.hrs.domain.staffing.Project;
import com.hexacta.hrs.domain.staffing.assignment.EmployeeAssignment;
import com.hexacta.hrs.repositories.employee.EmployeeRepository;

/**
 * @author mfunes
 * 
 */
public class EmployeeRecordObjectRepository extends HibernateRecordObjectRepository<Employee> implements
        EmployeeRepository {

    /** {@inheritDoc} */
    public EmployeeRecordObjectRepository(final Class<Employee> entityClass) {
        super(entityClass);
    }

    /** {@inheritDoc} */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<Employee> matching(final Employee criteria) {
        Criteria crit = getSession().createCriteria(Employee.class);

        if (criteria.getDateEnd() != null) {
            crit.add(Restrictions.isNotNull("dateEnd"));
            // Debo nullear la fecha para que el byExample lo ignore.
            criteria.setDateEnd(null);
        }

        if (criteria.getPerson() != null) {
            crit.add(Restrictions.eq("person.id", criteria.getPerson().getId()));
        }
        return crit.list();
    }

    /** {@inheritDoc} */
    @SuppressWarnings("unchecked")
    @Override
    public Employee searchBy(final User user) {
        Criteria crit = getSession().createCriteria(Employee.class).createCriteria("person").createCriteria("user");
        crit.add(Restrictions.idEq(user.getId()));

        List<Employee> employees = crit.list();
        Employee employee = null;

        if (!employees.isEmpty()) {
            employee = employees.get(0);
        }
        return employee;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getEvaluators
     * (org.vectrics.employee.Employee, java.util.Date, java.util.Date,
     * com.hexacta.hrs.domain.staffing.Project)
     */
    @SuppressWarnings("unchecked")
    @Override
    public Collection<Employee> getEvaluators(final Employee employee, final String period, final Project project,
            final int weight) {

        Query queryEvaluators = getSession().createQuery(
                "SELECT distinct ea.employee FROM EmployeeAssignment ea  "
                        + "WHERE ea.project.id = :projectId and "
                        + "	year(ea.startDate) <= :period  and year (ea.endDate) >= :period and "
                        + "   ea.employee.function.weight >= 4 and " // Arquitecto
                                                                     // = 4 // 4
                        + "  :employeeWeight > 0 and " + " ea.employee.function.weight > :employeeWeight"
                        + " and ea.employee.dateEnd is null");
        queryEvaluators.setLong("projectId", project.getId());
        queryEvaluators.setInteger("employeeWeight", weight);
        queryEvaluators.setString("period", period);

        Collection<Employee> listEvaluators = queryEvaluators.list();
        return listEvaluators;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Collection<Employee> getActiveEmployees() {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.isNull("dateEnd"));
        List<Employee> listToEvaluate = criteria.list();
        return listToEvaluate;
    }

    public Collection<Employee> getActiveEmployeesWithAntiquity() {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.isNull("dateEnd"));

        Calendar actualDate = Calendar.getInstance();
        actualDate.add(Calendar.DAY_OF_MONTH, -90);
        Date comparationDate = actualDate.getTime();

        criteria.add(Restrictions.lt("dateStart", comparationDate));
        List<Employee> listToEvaluate = criteria.list();
        return listToEvaluate;
    }

    @Override
    public Employee getEmployeeByPersonId(final Long personId) {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.eq("person.id", personId));
        return (Employee) criteria.uniqueResult();
    }

    @Override
    public List<MedicalInsurance> getEmployeesMedicalInsurance() {
        Criteria criteria = getSession().createCriteria(MedicalInsurance.class);
        return criteria.list();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.hexacta.hrs.repositories.employee.EmployeeRepository#
     * getAssignableEmployees()
     */
    @Override
    public Collection<Employee> getAssignableEmployees() {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.isNull("dateEnd"));
        criteria.add(Restrictions.eq("elegibleProjectMember", true));
        return criteria.list();
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getActiveEmployees
     * (java.util.Date, java.util.Date)
     */
    @Override
    public Collection<Employee> getActiveEmployees(final Date from, final Date to) {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.or(Restrictions.ge("dateEnd", from), Restrictions.isNull("dateEnd")));
        criteria.add(Restrictions.le("dateStart", to));
        List<Employee> listToEvaluate = criteria.list();
        return listToEvaluate;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.hexacta.hrs.repositories.employee.EmployeeRepository#
     * getAssignableEmployees(java.util.Date, java.util.Date)
     */
    @Override
    public Collection<Employee> getAssignableEmployees(final Date from, final Date to) {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.or(Restrictions.ge("dateEnd", from), Restrictions.isNull("dateEnd")));
        criteria.add(Restrictions.le("dateStart", to));
        Criteria ehCrit = criteria.createCriteria("employmentConditionHistory", "eh");
        ehCrit.createCriteria("assignabilityStatuses", "as").add(Restrictions.eq("available", true))
                .add(Restrictions.le("effectiveDate", to))
                .add(Restrictions.or(Restrictions.ge("endDate", from), Restrictions.isNull("endDate")));
        return criteria.list();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.hexacta.hrs.repositories.employee.EmployeeRepository#
     * getAssignableEmployees(java.util.Date, java.util.Date,
     * java.lang.String[])
     */
    @Override
    public Collection<Employee> getAssignableEmployees(final Date from, final Date to, final String[] roles) {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.or(Restrictions.ge("dateEnd", from), Restrictions.isNull("dateEnd")));
        criteria.add(Restrictions.le("dateStart", to));
        Criteria ehCrit = criteria.createCriteria("employmentConditionHistory", "eh");
        ehCrit.createCriteria("assignabilityStatuses", "as").add(Restrictions.eq("available", true))
                .add(Restrictions.le("effectiveDate", to))
                .add(Restrictions.or(Restrictions.ge("endDate", from), Restrictions.isNull("endDate")));

        if (roles != null && roles.length != 0) {
            Criteria roleCriteria = criteria.createCriteria("function");
            roleCriteria.add(Restrictions.in("code", roles));
        }
        return criteria.list();
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getActiveEmployees
     * (java.util.Date, java.util.Date, java.lang.String[])
     */
    @Override
    public Collection<Employee> getActiveEmployees(final Date from, final Date to, final String[] roles) {
        Criteria criteria = getSession().createCriteria(Employee.class);
        criteria.add(Restrictions.or(Restrictions.ge("dateEnd", from), Restrictions.isNull("dateEnd")));
        criteria.add(Restrictions.le("dateStart", to));

        if (roles != null && roles.length != 0) {
            Criteria roleCriteria = criteria.createCriteria("function");
            roleCriteria.add(Restrictions.in("code", roles));
        }

        List<Employee> listToEvaluate = criteria.list();
        return listToEvaluate;
    }

    @Override
    public List<Object> getEmployeesByOffice() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, Count(*) FROM Employee e " + "	WHERE e.dateEnd is null "
                        + "GROUP BY e.location");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByGender() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, e.person.gender as Gender,  Count(*) FROM Employee e "
                        + "	WHERE e.dateEnd is null " + "GROUP BY e.person.gender, e.location ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesWithChildren() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, e.person.gender as Gender, Count(*) FROM FamiliarRelationship fr "
                        + " JOIN  fr.employee e "
                        + "	WHERE (e.dateEnd is null) AND (e.person.id = fr.employee.person.id) "
                        + "		AND (fr.familiarRelationshipType = 'HIJO' OR fr.familiarRelationshipType = 'HIJA') "
                        + "GROUP BY e.person.gender, e.location ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesMarried() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, e.person.gender as Gender, Count(*) FROM FamiliarRelationship fr "
                        + " JOIN  fr.employee e "
                        + "	WHERE (e.dateEnd is null) AND (e.person.id = fr.employee.person.id) "
                        + "		AND (fr.familiarRelationshipType = 'CONYUGE') " + "GROUP BY e.person.gender, e.location ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByJourney() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, e.hoursPerDay as Hours,  Count(*) FROM Employee e "
                        + "	WHERE e.dateEnd is null " + "GROUP BY e.hoursPerDay, e.location ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByDegree() {
        Query query = getSession().createQuery(
                "SELECT e.location as Oficce, s.status as Status, Count(*) FROM Employee e "
                        + " JOIN  e.person.studies s " + "	WHERE (e.dateEnd is null) "
                        + "GROUP BY e.location, s.status ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesBySeniorityAndArea() {
        Query query = getSession().createQuery(
                "SELECT  ft.area, f.description as Seniority, Count(*) "
                        + "		FROM Employee e LEFT JOIN e.function f JOIN e.function.JobFunctionType ft "
                        + "	WHERE e.dateEnd is null " + "GROUP BY f.description, ft.area");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesBySeniorityAndGender() {
        Query query = getSession().createQuery(
                "SELECT e.person.gender, f.description as Seniority, Count(f.description) "
                        + "		FROM Employee e JOIN e.function f" + "	WHERE e.dateEnd is null "
                        + "GROUP BY e.person.gender, f.description ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByLanguage() {
        Query query = getSession().createQuery(
                "SELECT e.person.programmingLanguage as Language, Count(*) FROM Employee e "
                        + "	WHERE e.dateEnd is null " + "GROUP BY e.person.programmingLanguage ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByEnglishLevel() {
        Query query = getSession().createQuery(
                "SELECT e.person.englishLevel as Level, Count(*) FROM Employee e " + "	WHERE e.dateEnd is null "
                        + "GROUP BY e.person.englishLevel ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesByAntiquity() {
        Query query = getSession().createQuery(
                "SELECT YEAR( FROM_DAYS( DATEDIFF( NOW(), dateStart ))) as anios, COUNT(*) as cantidad "
                        + "	FROM Employee e " + "		WHERE e.dateEnd is null "
                        + " GROUP BY  YEAR( FROM_DAYS( DATEDIFF( NOW(), e.dateStart ))) ");

        return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object> getEmployeesByAge() {
        Query query = getSession().createQuery(
                "SELECT YEAR( FROM_DAYS( DATEDIFF( NOW(), e.person.dateOfBirth ))) as anios, COUNT(*) as cantidad "
                        + "	FROM Employee e " + "		WHERE e.dateEnd is null "
                        + " GROUP BY  YEAR( FROM_DAYS( DATEDIFF( NOW(), e.person.dateOfBirth ))) ");

        return query.list();
    }

    @Override
    public List<Object> getEmployeesFlow(final Calendar begin, final Calendar end) {
        Query query = getSession().createQuery(
                "SELECT COUNT(e.dateStart) as Ingresos, COUNT(e.dateEnd) as Bajas " + " FROM Employee e "
                        + " WHERE (e.dateStart > :dateFrom) OR (e.dateEnd >:dateTo) ");

        query.setCalendar("dateFrom", begin);
        query.setCalendar("dateTo", end);

        return query.list();
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getEvaluables
     * (org.vectrics.employee.Employee, java.lang.String,
     * com.hexacta.hrs.domain.staffing.Project, int)
     */
    @Override
    public Collection<EmployeeAssignment> getEvaluables(final Employee evaluator, final String period,
            final Project project) {

        // El evaluador tiene que ser por lo menos arquitecto (peso 4)
        if (evaluator == null || evaluator.getFunction().getWeight() < 4)
            return new ArrayList<EmployeeAssignment>();

        int weight = evaluator.getFunction().getWeight();

        if (weight <= 0)
            return new ArrayList<EmployeeAssignment>();

        Criteria crit = getSession().createCriteria(EmployeeAssignment.class);
        crit.add(Restrictions.eq("project", project));
        crit.add(Restrictions.le("startDate", new GregorianCalendar(Integer.valueOf(period), Calendar.DECEMBER, 31)));
        crit.add(Restrictions.ge("endDate", new GregorianCalendar(Integer.valueOf(period), Calendar.JANUARY, 1)));

        Criteria employeeCrit = crit.createCriteria("employee");
        employeeCrit.add(Restrictions.isNull("dateEnd"));
        employeeCrit.createCriteria("function").add(Restrictions.lt("weight", weight));

        Collection<EmployeeAssignment> listEvaluables = crit.list();
        return listEvaluables;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getEnteringEmployees
     * (java.util.Calendar, java.util.Calendar)
     */
    @Override
    public String getEnteringEmployees(final Calendar begin, final Calendar end) {
        Query query = getSession().createQuery(
                "SELECT COUNT(e.dateStart) as Ingresos FROM Employee e "
                        + " WHERE e.dateStart BETWEEN :dateFrom AND :dateTo ");

        query.setCalendar("dateFrom", begin);
        query.setCalendar("dateTo", end);

        Long employees = (Long) query.uniqueResult();
        return employees.toString();
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * com.hexacta.hrs.repositories.employee.EmployeeRepository#getExitingEmployees
     * (java.util.Calendar, java.util.Calendar)
     */
    @Override
    public String getExitingEmployees(final Calendar begin, final Calendar end) {
        Query query = getSession()
                .createQuery(
                        "SELECT COUNT(e.dateEnd) as Bajas FROM Employee e "
                                + " WHERE e.dateEnd BETWEEN :dateFrom AND :dateTo ");

        query.setCalendar("dateFrom", begin);
        query.setCalendar("dateTo", end);

        Long employees = (Long) query.uniqueResult();
        return employees.toString();
    }

	@Override
	public List<Employee> getPartners() {
		Criteria crit = getSession().createCriteria(Employee.class);
        crit.add(Restrictions.isNull("dateEnd"));
		
        Criteria roleCriteria = crit.createCriteria("function");
        roleCriteria.add(Restrictions.eq("code", "Socio"));
		return crit.list();
	}
    
}
